﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MonitorPlan.Model
{
    // extended user class from Person class
    public class  User: CoreLibrary.Person 
    {
        public string Password { get; set; }
        public string UserName { get; set; }
        public string UserType { get; set; }
        public int HouseCount { get; set; }
        public int IsDeleted { get; set; }
        public User():base()
        {
            this.Password = string.Empty;
            this.UserName = string.Empty;
            this.UserType = string.Empty;
            this.IsDeleted = 0;
        }
        // update a user by its id
        public bool UpdateUser(string UserId, string Password)
        {
            bool State = false;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserId", UserId);
            cmd.Parameters.AddWithValue("@FirstName", this.FirstName);
            cmd.Parameters.AddWithValue("@LastName", this.LastName);
            cmd.Parameters.AddWithValue("@Gender", this.Gender);
            cmd.Parameters.AddWithValue("@Address", this.Address);
            cmd.Parameters.AddWithValue("@UserName", this.UserName);
            cmd.Parameters.AddWithValue("@UserType", this.UserType);

            if (Password=="")
            {
                cmd.CommandText = "modifyUserNoPassword";
                State = new ADO.DataAdapter().ExecNonPro(cmd);
            }
            else
            {
                cmd.CommandText = "modifyUser";
                cmd.Parameters.AddWithValue("@Password", CoreLibrary.CoreSecurity.getMd5Hash(this.Password));
                State = new ADO.DataAdapter().ExecNonPro(cmd);
            }
            
            return State;
        }

        // the method to insert new user into table users
        // insert one by one
        public bool Add()
        {
            bool State = false;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "addUser";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@FirstName", this.FirstName);
            cmd.Parameters.AddWithValue("@LastName", this.LastName);
            cmd.Parameters.AddWithValue("@Gender", this.Gender);
            cmd.Parameters.AddWithValue("@Address", this.Address);
            cmd.Parameters.AddWithValue("@UserName", this.UserName);
            cmd.Parameters.AddWithValue("@Password", CoreLibrary.CoreSecurity.getMd5Hash(this.Password));
            cmd.Parameters.AddWithValue("@UserType", this.UserType);

            State = new ADO.DataAdapter().ExecNonPro(cmd);
            return State;
        }
        // the method to get only one user from table users in database.
        // this method return user based on the user id you passed to it.
        public User GetUser(int Id)
        {
            User _User=new User();
            string sql = @"select * from users where userid="+Id;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            //cmd.Parameters.AddWithValue("@userid", Id);
            DataRow dr = new DataTable().NewRow();
            ADO.DataAdapter da = new ADO.DataAdapter();
            DataTable dt = new DataTable();
            dt = da.RunQuery(cmd);
            if (dt.Rows.Count>0)
            {
                dr = dt.Rows[0];
                _User.Id = Convert.ToInt32(dr[0]);
                _User.FirstName = dr[1].ToString();
                _User.LastName = dr[2].ToString();
                _User.Gender = dr[3].ToString();
                _User.Email = dr[4].ToString();
                _User.Phone = dr[5].ToString();
                _User.Address = dr[6].ToString();
                _User.UserName = dr[7].ToString();
                _User.Password = dr[8].ToString();
                _User.UserType = dr[9].ToString();
                _User.IsDeleted = Convert.ToInt32(dr[10]);
                _User.HouseCount = new House().HouseCount(_User.Id);
            }
            return _User;
        }
        // method to get user by username
        // this is the overloading method
        public User GetUser(string UserName)
        {
            User _User = new User();
            string sql = @"select * from users where username='"+UserName+"'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
           // cmd.Parameters.AddWithValue("@user", UserName);
            DataRow dr = new DataTable().NewRow();
            ADO.DataAdapter da = new ADO.DataAdapter();
            DataTable dt = new DataTable();
            dt = da.RunQuery(cmd);
            if (dt.Rows.Count > 0)
            {
                dr = dt.Rows[0];
                _User.Id = Convert.ToInt32(dr[0]);
                _User.FirstName = dr[1].ToString();
                _User.LastName = dr[2].ToString();
                _User.Gender = dr[3].ToString();
                _User.Email = dr[4].ToString();
                _User.Phone = dr[5].ToString();
                _User.Address = dr[6].ToString();
                _User.UserName = dr[7].ToString();
                _User.Password = dr[8].ToString();
                _User.UserType = dr[9].ToString();
                _User.IsDeleted = Convert.ToInt32(dr[10]);
                _User.HouseCount = new House().HouseCount(_User.Id);
            }
            return _User;
        }

        // method to get a list of users
        public List<User> GetUsers(SqlCommand cmd)
        {
            List<User> UserList = new List<User>();
            ADO.DataAdapter da = new ADO.DataAdapter();
            DataTable dt = da.RunQuery(cmd);
            User u;
            if(dt.Rows.Count>0)
            {
                foreach(DataRow row in dt.Rows)
                {
                    u = new User();
                    u.Id = Convert.ToInt32(row[0]);
                    u.FirstName = row[1].ToString();
                    u.LastName = row[2].ToString();
                    u.Gender = row[3].ToString();
                    u.Email = row[4].ToString();
                    u.Phone = row[5].ToString();
                    u.Address = row[6].ToString();
                    u.UserName = row[7].ToString();
                    u.Password = row[8].ToString();
                    u.UserType = row[9].ToString();
                    u.IsDeleted = Convert.ToInt32(row[10]);
                    UserList.Add(u);
                }
            }
            return UserList;
        }
        // method to remove a user from table users.
        // the user is remove based on the userid you passed to it.
        public bool Remove(int Id)
        {
            bool State = false;
            //string sql = "update users set isdeleted=1 where userid=@userid";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "removeUser";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserId", Id);
            State = new ADO.DataAdapter().ExecNonPro(cmd);
            return State;
        }
        // the method to remove more than one users at a time based
        // the sql command
        public bool RemoveAll(SqlCommand cmd)
        {
            bool State = false;
            ADO.DataAdapter da = new ADO.DataAdapter();
            State = da.RunNonQuery(cmd);
            return State;
        }
        // the method to update any users int table users
        public bool Update(SqlCommand cmd)
        {
            bool State = false;
            ADO.DataAdapter da = new ADO.DataAdapter();
            State = da.RunNonQuery(cmd);
            return State;
        }
    }
    // class location
    public class Location: CoreLibrary.BaseObject
    {
        public Location(): base()
        {

        }
        public bool Add()
        {
            bool State = false;
            string sql = @"insert into location(locationname,description,isdeleted) ";
            sql +=@"values(@name,@desc,@isdeleted)";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@name", this.Name);
            cmd.Parameters.AddWithValue("@desc", this.Description);
            cmd.Parameters.AddWithValue("@isdeleted", this.IsDeleted);
            ADO.DataAdapter da = new ADO.DataAdapter();
            State = da.RunNonQuery(cmd);
            return State;
        }
        // get location by its id
        public Location getLocation(int LocationId)
        {
            Location lc = new Location();
            string sql = "select * from location where locationid=" + LocationId;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            ADO.DataAdapter da = new ADO.DataAdapter();
            DataTable dt = new DataTable();
            dt = da.RunQuery(cmd);
            DataRow dr = dt.NewRow();
            if(dt.Rows.Count>0)
            {
                dr = dt.Rows[0];
                lc.Id = LocationId;
                lc.Name = dr[1].ToString();
                lc.Description = dr[2].ToString();
                lc.IsDeleted = Convert.ToInt32(dr[3]);
            }
            return lc;
        }
        // update location by its id
        public bool Update(int LocationId)
        {
            bool State = false;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "update location set locationname=@name, description=@desc where locationid=" + LocationId;
            cmd.Parameters.AddWithValue("@name", this.Name);
            cmd.Parameters.AddWithValue("@desc", this.Description);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        // remove the location by its id
        public bool Remove(int LocationId)
        {
            bool State = false;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "delete from location where locationid=@id";
            cmd.Parameters.AddWithValue("@id", LocationId);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
    }
    // class house
    public class House: CoreLibrary.BaseObject
    {
        public int OwnerId { get; set; }
        public int LocationId { get; set; }
        public string OwnerName { get; set; }
        public string LocationName { get; set; }
        public House(): base()
        {
            this.OwnerId = 0;
            this.LocationId = 0;
            this.LocationName = "";
            this.OwnerName = "";
        }
        // add new house to database
        public bool Add()
        {
            bool State = false;
            string sql = @"insert into house(housename,userid,locationid,description)";
            sql += " values(@hname,@ownerid,@locid,@desc)";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@hname", this.Name);
            cmd.Parameters.AddWithValue("@ownerid", this.OwnerId);
            cmd.Parameters.AddWithValue("@locid", this.LocationId);
            cmd.Parameters.AddWithValue("@desc", this.Description);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        // get number of house for each user
        public int HouseCount(int UserId)
        {
            int count = 0;
            string sql = "select * from house where userid=" + UserId;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            count = dt.Rows.Count;
            return count;
        }
        // get house by its id
        public House getHouse(int HouseId)
        {
            House h = new House();
            string sql = "select house.houseid, house.housename,house.description, concat(users.firstname,' ',users.lastname) as owner, location.locationname, house.userid,house.locationid, house.isdeleted ";
            sql += "from house inner join users on users.userid = house.userid inner join location on house.locationid=location.locationid and house.houseid=" + HouseId;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            DataRow dr = dt.NewRow();
            if(dt.Rows.Count>0)
            {
                dr = dt.Rows[0];
                h.Id = Convert.ToInt32(dr[0]);
                h.Name = dr[1].ToString();
                h.Description = dr[2].ToString();
                h.OwnerName = dr[3].ToString();
                h.LocationName = dr[4].ToString();
                h.OwnerId = Convert.ToInt32(dr[5]);
                h.LocationId = Convert.ToInt32(dr[6]);
                h.IsDeleted = Convert.ToInt32(dr[7]);
            }
            return h;
        }
        // remove house by its id
        public bool Remove(int HouseId)
        {
            bool State = false;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "delete from house where houseid=@id";
            cmd.Parameters.AddWithValue("@id", HouseId);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        public bool Update(int HouseId)
        {
            bool State = false;
            string sql = @"update house set housename=@hname, userid=@uid, locationid=@locid, description=@desc where houseid=@id";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@hname", this.Name);
            cmd.Parameters.AddWithValue("@uid", this.OwnerId);
            cmd.Parameters.AddWithValue("@locid", this.LocationId);
            cmd.Parameters.AddWithValue("@desc", this.Description);
            cmd.Parameters.AddWithValue("@id", HouseId);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
    }
    // class room
    public class Room: CoreLibrary.BaseObject
    {
        public int HouseId { get; set; }
        public string HouseName { get; set; }
        public Room() : base()
        {
            this.HouseId = 0;
        }
        // add new room to database
        public bool Add()
        {
            bool State = false;
            string sql = @"insert into room(roomname, houseid,description) values(@rname,@hid,@desc)";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@rname", this.Name);
            cmd.Parameters.AddWithValue("@hid", this.HouseId);
            cmd.Parameters.AddWithValue("@desc", this.Description);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        // remove room by its id
        public bool Remove(int RoomId)
        {
            bool State = false;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "delete from room where roomid=@id";
            cmd.Parameters.AddWithValue("@id", RoomId);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        // get room by its id
        public Room getRoom(int RoomId)
        {
            Room r = new Room();
            string sql = "select room.roomid, room.roomname,house.housename,room.[description], house.houseid,room.isdeleted ";
            sql += "from room inner join house on room.houseid = house.houseid ";
            sql += "where room.isdeleted=0 and room.roomid=" + RoomId;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            DataRow dr = dt.NewRow();
            if(dt.Rows.Count>0)
            {
                dr = dt.Rows[0];
                r.Id = Convert.ToInt32(dr[0]);
                r.Name = dr[1].ToString();
                r.HouseName = dr[2].ToString();
                r.Description = dr[3].ToString();
                r.HouseId = Convert.ToInt32(dr[4]);
                r.IsDeleted = Convert.ToInt32(dr[5]);
            }
            return r;
        }
        // update room by its id
        public bool Update(int RoomId)
        {
            bool state = false;
            string sql = @"update room set roomname=@rname, houseid=@hid,description=@desc where roomid=@id";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@rname", this.Name);
            cmd.Parameters.AddWithValue("@hid", this.HouseId);
            cmd.Parameters.AddWithValue("@desc", this.Description);
            cmd.Parameters.AddWithValue("@id", RoomId);
            state = new ADO.DataAdapter().RunNonQuery(cmd);
            return state;
        }
    }
    // class tool
    public class Tool: CoreLibrary.BaseObject
    {
        new public string Id { get; set; }
        public int RoomId { get; set; }
        public int HouseId { get; set; }
        public string ToolType { get; set; }
        public string KeyOn { get; set; }
        public String KeyOff { get; set; }
        // get tool by its id and house id
        public Tool GetTool(string ToolId, string HouseId)
        {
            Tool tool = new Tool();
            string sql = @"select * from tool where toolid='" + ToolId + "' and houseid=" + HouseId;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            DataRow dr = dt.NewRow();
            if(dt.Rows.Count>0)
            {
                dr = dt.Rows[0];
                tool.Id = dr[0].ToString();
                tool.Name = dr[1].ToString();
                tool.ToolType = dr[2].ToString();
                tool.KeyOn = dr[3].ToString();
                tool.KeyOff = dr[4].ToString();
                tool.HouseId = Convert.ToInt32(dr[5]);
                tool.Description = dr[6].ToString();
                tool.IsDeleted = Convert.ToInt32(dr[7]);
                tool.RoomId = Convert.ToInt32(dr[8]);
            }
            return tool;
        }
        // remove tool by its id
        public bool Remove(String ToolId)
        {
            bool State = false;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "delete from tool where toolid=@id";
            cmd.Parameters.AddWithValue("@id", ToolId);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        // add new tool
        public bool Add()
        {
            bool State = false;
            string sql = @"insert into tool(toolid, toolname,tooltype,keyon,keyoff,houseid,description,roomid) ";
            sql += "values(@tid,@tname,@ttype,@ton,@toff,@hid,@desc,@rid)";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@tid", this.Id);
            cmd.Parameters.AddWithValue("@tname", this.Name);
            cmd.Parameters.AddWithValue("@ttype", this.ToolType);
            cmd.Parameters.AddWithValue("@ton", this.KeyOn);
            cmd.Parameters.AddWithValue("@toff", this.KeyOff);
            cmd.Parameters.AddWithValue("@hid", this.HouseId);
            cmd.Parameters.AddWithValue("@desc", this.Description);
            cmd.Parameters.AddWithValue("@rid", this.RoomId);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        // check keyon and off to make sure it's not used
        // return true if not use, else return false
        public bool CheckKey()
        {
            bool State = false;
            string sql = @"select count(toolid) from tool where houseid=" + this.HouseId;
            sql += " and ((keyon='" + this.KeyOn + "' or keyoff='" + this.KeyOff + "') or (keyon='";
            sql += this.KeyOff +"' or keyoff='" + this.KeyOn +"'))";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new DataTable();
            dt = new ADO.DataAdapter().RunQuery(cmd);
            int count=0;
            foreach(DataRow dr in dt.Rows)
            {
                count = Convert.ToInt32(dr[0].ToString());
            }
            if(count > 0)
            {
                State = false;
            }
            else
                State = true;

            return State;
        }

        // is key on or off
        // return on or off or invalid
        public string IsKey(int hid, string key)
        {
            string State = "";
            // is on?
            string sql = "select * from tool where houseid=" + hid + " and keyon='" + key + "'";
            // is off?
            string sql1 = "select * from tool where houseid=" + hid + " and keyoff='" + key + "'";
            SqlCommand cmd = new SqlCommand();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = sql1;
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            DataTable dt1 = new ADO.DataAdapter().RunQuery(cmd1);
            if(dt.Rows.Count>0)
            {
                State = "on";
            }
            else
            {
                if(dt1.Rows.Count>0)
                {
                    State = "off";
                }
                else
                {
                    State = "invalid";
                }
            }
            return State;
        }

        // get toolid by its key
        public string GetToolIdByKey(int hid, string key)
        {
            string tid = "";

            string sql = "select toolid from tool where houseid=" + hid + "and (keyon='" + key + "' or keyoff='" + key + "')";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            foreach (DataRow r in dt.Rows)
            {
                tid = r[0].ToString();
            }
            return tid;
        }
    }

    // Contact class
    public class Contact: CoreLibrary.BaseObject
    {
        public int UserId { get; set; }
        public string UserName { get; set; }
        public string ContactType { get; set; }

        // remove contact by its id
        public bool Remove(string ContactId)
        {
            bool State = false;
            string sql = @"delete from contact where contactid=@id";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@id", ContactId);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        public bool Add()
        {
            bool State = false;
            string sql = @"insert into contact(userid,contact,contacttype) values(@uid,@con,@contype)";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@uid", this.UserId);
            cmd.Parameters.AddWithValue("@con", this.Name);
            cmd.Parameters.AddWithValue("@contype", this.ContactType);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }

        // update a contact by its id
        public bool Update(string ContactId)
        {
            bool State = false;
            string sql = @"update contact set userid=@uid, contact=@con, contacttype=@contype where contactid=@conid";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@uid", this.UserId);
            cmd.Parameters.AddWithValue("@con", this.Name);
            cmd.Parameters.AddWithValue("@contype", this.ContactType);
            cmd.Parameters.AddWithValue("@conid", this.Id);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        // get phone number of a house, return as series of phone number
        public string GetPhone(string HouseId)
        {
            string Phone = "";
            string sql1 = @"select contact.*, house.userid from contact inner join house on contact.userid=house.userid where house.houseid=" + HouseId + " and contact.contacttype='phone'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql1;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            foreach (DataRow dr in dt.Rows)
            {
                Phone += dr[2].ToString() + ",";
            }
            // remove the last ,
            if(Phone!="")
            {
                Phone = Phone.Substring(0, Phone.Length - 1);
            }
            return Phone;
        }
        // get a contact by its id
        public Contact GetContact(string ContactId)
        {
            Contact con = new Contact();
            string sql = @"select * from contact where contactid=" + ContactId;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            DataRow dr = dt.NewRow();
            if(dt.Rows.Count>0)
            {
                dr = dt.Rows[0];
                con.Id = Convert.ToInt32(ContactId);
                con.Name = dr[2].ToString();
                con.UserId = Convert.ToInt32(dr[1]);
                con.ContactType = dr[3].ToString();
            }
            return con;
        }

    }

    // the notification class
    public class Notification:CoreLibrary.BaseObject
    {
        public string ToolId { get; set; }
        public int HouseId { get; set; }
        public int UserId { get; set; }
        public string WhenToNotify { get; set; }
        public string IsSms { get; set; }
        public string IsAlarm { get; set; }
        public string StartTime { get; set; }
        public string EndTime { get; set; }

        // remove a notification by its id
        public bool Remvoe(string NotificationId)
        {
            bool State = false;
            string sql = @"delete from notification where notificationid=@nid";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@nid", NotificationId);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        // add a new notification
        public bool Add()
        {
            bool State = false;
            string sql = @"insert into notification(toolid,houseid,whentonotify,userid, issms,isalarm,starttime,endtime) values(@tid,@hid,@wh,@uid,@sms,@alarm,@st,@et)";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@tid", this.ToolId);
            cmd.Parameters.AddWithValue("@hid", this.HouseId);
            cmd.Parameters.AddWithValue("@wh", this.WhenToNotify);
            cmd.Parameters.AddWithValue("@uid", this.UserId);
            cmd.Parameters.AddWithValue("@sms", this.IsSms);
            cmd.Parameters.AddWithValue("@alarm", this.IsAlarm);
            cmd.Parameters.AddWithValue("@st", this.StartTime);
            cmd.Parameters.AddWithValue("@et", this.EndTime);
            var cmd1 = new SqlCommand();
            cmd1.CommandText = "select * from [notification] where toolid='" + this.ToolId + "'";
            DataTable dt = new DataTable();
            dt = new ADO.DataAdapter().RunQuery(cmd1);
            if (dt.Rows.Count < 2)
            {
                State = new ADO.DataAdapter().RunNonQuery(cmd);
            }
            bool t = Convert.ToBoolean(1);
            return State;
        }
        // update a notification by its id
        public bool Update(string NotificationId)
        {
            bool State = false;
            string sql = @"update notification set toolid=@tid, houseid=@hid, whentonotify=@wh,userid=@uid,issms=@sms,isalarm=@alarm,starttime=@st,endtime=@et where notificationid=@nid";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@tid", this.ToolId);
            cmd.Parameters.AddWithValue("@hid", this.HouseId);
            cmd.Parameters.AddWithValue("@wh", this.WhenToNotify);
            cmd.Parameters.AddWithValue("@uid", this.UserId);
            cmd.Parameters.AddWithValue("@nid", this.Id);
            cmd.Parameters.AddWithValue("@sms", this.IsSms);
            cmd.Parameters.AddWithValue("@alarm", this.IsAlarm);
            cmd.Parameters.AddWithValue("@st", this.StartTime);
            cmd.Parameters.AddWithValue("@et", this.EndTime);
            State = new ADO.DataAdapter().RunNonQuery(cmd);
            return State;
        }
        // get a notification by its id
        public Notification GetNotification(string NotificationId)
        {
            Notification not = new Notification();
            string sql = @"select * from notification where notificationid=" + NotificationId;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            DataRow dr = dt.NewRow();
            if(dt.Rows.Count>0)
            {
                dr = dt.Rows[0];
                not.Id = Convert.ToInt32(NotificationId);
                not.ToolId = dr[1].ToString();
                not.HouseId = Convert.ToInt32(dr[2]);
                not.WhenToNotify = dr[3].ToString();
                not.UserId = Convert.ToInt32(dr[4]);
                not.IsSms = dr[5].ToString();
                not.IsAlarm = dr[6].ToString();
                not.StartTime = dr[7].ToString();
                not.EndTime = dr[8].ToString();
            }
            return not;
        }
        // send email notification by houseid and toolid
        public void Notify(string HouseId, string ToolId, string WhenToNotify)
        {
            // send email notification when the tool is on
            string sql = "";
            string subject = "";
            string body = "";
            // get house name
            
            Model.House h = new House().getHouse(Convert.ToInt32(HouseId));
            string HouseName = h.Name;
            Model.Tool tool = new Tool().GetTool(ToolId, HouseId);
            string ToolName = tool.Name;
            if(WhenToNotify=="on")
            {
                // set mail parameter when a tool is on
                subject = "Meshtech: One tool is ON";
                body = "<h3 style='color: red'>A tool is turned ON</h3>";
                body += "<p>House Id: <b>" + HouseId + "</b></p>";
                body += "<p>House Name: <b>" + HouseName + "</b></p>";
                body +="<p>Tool Id: <b>" + ToolId + "</b></p>";
                body += "<p>Tool Name: <b>" + ToolName + "</b></p>";
                body +="<p>Date: <b>" + DateTime.Now.ToShortDateString() + "</b></p>";
                body +="<p>Time: <b>" + DateTime.Now.ToShortTimeString() + "</b></p>";
                // get the notification the specified tool   
                sql = "select * from notification where whentonotify='on' and houseid=" + HouseId + " and toolid='" + ToolId+"'";

            }
            // send email when the tool is off
            else if(WhenToNotify=="off")
            {
                // set mail parameter when a tool is off
                subject = "Meshtech: One tool is OFF";
                body = "<h3 style='color: red'>A tool is turned OFF</h3>";
                body += "<p>House Id: <b>" + HouseId + "</b></p>";
                body += "<p>House Name: <b>" + HouseName + "</b></p>";
                body += "<p>Tool Id: <b>" + ToolId + "</b></p>";
                body += "<p>Tool Name: <b>" + ToolName + "</b></p>";
                body += "<p>Date: <b>" + DateTime.Now.ToShortDateString() + "</b></p>";
                body += "<p>Time: <b>" + DateTime.Now.ToShortTimeString() + "</b></p>";
                // get the notification the specified tool   
                sql = "select * from notification where whentonotify='off' and houseid=" + HouseId + " and toolid='" + ToolId + "'";
            }
            else
            { 
                // do nothing
                sql = "select * from [notification] where 1=0";
            }

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            DataRow dr = dt.NewRow();
            if (dt.Rows.Count == 1)
            {
                // get email and send here
                string sql1 = @"select contact.*, house.userid from contact inner join house on contact.userid=house.userid where house.houseid=" + HouseId + " and contact.contacttype='email'";
                SqlCommand cmd1 = new SqlCommand();
                cmd1.CommandText = sql1;
                DataTable dt1 = new ADO.DataAdapter().RunQuery(cmd1);
                foreach (DataRow dr1 in dt1.Rows)
                {
                    var sms = new CoreLibrary.Messaging();
                    sms.SendMail(dr1[2].ToString(), subject, body);
                }
            }
        }
        // if a tool is needed to send sms or not, true send and false not send
        public bool NotifySms(string HouseId, String ToolId, String WhenToNotify)
        {
            bool IsSms = false;
            string sql;
            // send sms when the tool is on
            if(WhenToNotify=="on")
            {
                sql = @"select * from [notification] where houseid=" + HouseId + " and toolid='" + ToolId + "' and issms='Yes' and whentonotify='on'";
            }
            // send sms when the tool is off
            else if(WhenToNotify=="off")
            {
                sql = @"select * from [notification] where houseid=" + HouseId + " and toolid='" + ToolId + "' and issms='Yes' and whentonotify='off'";
            }
            else
            {
                sql = "select * from [notification] where 1=0";
            }
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            DataRow dr = dt.NewRow();
            if(dt.Rows.Count==1)
            {
                IsSms = true;
            }
            else
            {
                IsSms = false;
            }
            return IsSms;
        }

        // set alarm sound notification by houseid and toolid
        public bool NotifyAlarm(string HouseId, string ToolId, string WhenToNotify)
        {
            bool IsAlarm = false;
            string sql;
            // alarm when the tool is on
            if (WhenToNotify == "on")
            {
                sql = @"select * from [notification] where houseid=" + HouseId + " and toolid='" + ToolId + "' and isalarm='Yes' and whentonotify='on'";
            }
            // alarm when the tool is off
            else if(WhenToNotify=="off")
            {
                sql = @"select * from [notification] where houseid=" + HouseId + " and toolid='" + ToolId + "' and isalarm='Yes' and whentonotify='off'";
                
            }
            // do nothing
            else
            {
                sql = "select * from [notification] where 1=0";
            }

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            DataTable dt = new ADO.DataAdapter().RunQuery(cmd);
            DataRow dr = dt.NewRow();
            if(dt.Rows.Count ==1 )
            {
                dr = dt.Rows[0];
                //DateTime CurrentTime = DateTime.Now.ToLocalTime();
                //DateTime STime = Convert.ToDateTime(dr[7]).ToLocalTime();
                //DateTime ETime = Convert.ToDateTime(dr[8]).ToLocalTime();
                if(new CoreLibrary.Comparison().CompareTime(DateTime.Now.ToLocalTime().ToString(), dr[7].ToString(), dr[8].ToString())==true)
                {
                    IsAlarm = true;
                }
                else
                {
                    IsAlarm = false;
                }
                     
            }
            else
            {
                IsAlarm = false;
            }
            return IsAlarm;
        }
    }
    // class tool types
    public class ToolType:CoreLibrary.BaseObject
    {
        /// <summary>
        /// Get a table of tools based on its type and house id
        /// </summary>
        /// <param name="HouseId"></param>
        /// <param name="ToolType"></param>
        /// <returns></returns>
        public DataTable GetTools(string HouseId, string ToolType)
        {

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "getTools";
            cmd.Parameters.AddWithValue("@HouseId", HouseId);
            cmd.Parameters.AddWithValue("@ToolType", ToolType);
            return new ADO.DataAdapter().ExecPro(cmd);

        }

        /// <summary>
        /// Get all tool types of a house from table tool.
        /// </summary>
        /// <param name="HouseId">House Id as string</param>
        /// <returns></returns>
        public List<string> GetToolTypes(string HouseId)
        {
            List<string> Types = new List<string>();
            string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection con = new SqlConnection();
            con.ConnectionString = conStr;
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("select distinct tooltype from tool where houseid=" + HouseId,con);
                SqlDataReader dr = cmd.ExecuteReader();
                while(dr.Read())
                {
                    Types.Add(dr[0].ToString());
                }
                dr.Close();
                con.Close();
            }
            catch (Exception)
            {
                con.Close();
                
            }

            // a list of device types
            return Types;
        }
        /// <summary>
        /// Get the last transaction.
        /// </summary>
        /// <param name="HouseId"></param>
        /// <param name="ToolId"></param>
        /// <returns>Image name</returns>
        public string getLastTrans(string HouseId, string ToolId)
        {
            string str = "";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "getLastTrans";
            cmd.Parameters.AddWithValue("@ToolId", ToolId);
            cmd.Parameters.AddWithValue("@HouseId", HouseId);
            str = new ADO.DataAdapter().ExecSalarPro(cmd);
            return str;
        }
    }
}